Renaming, Removing, and Reordering Columns in Power BI

Course: Microsoft Power BI Training

Topic: Renaming, Removing, and Reordering Columns

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Column Management in Power BI

Column management is an important part of data preparation in Microsoft Power BI. When data is imported from Excel, CSV files, databases, APIs, or other sources, the column names and order may not be suitable for analysis and reporting.

Power Query Editor provides tools to rename columns, remove unnecessary columns, and rearrange columns into a logical sequence. These transformations help make datasets easier to understand, maintain, and prepare for data modeling.

The three main operations covered in this topic are:

• Renaming columns: Changing column headers to meaningful and understandable names.
• Removing columns: Deleting unnecessary fields that are not required for the intended analysis.
• Reordering columns: Changing the sequence in which columns appear in a table.

These operations are generally performed in Power Query Editor before the cleaned data is loaded into the Power BI data model.

Example

Suppose a company imports sales information with the following column names:

The table would be easier to understand if Column1 were renamed to Order ID, Column2 to Product, Column3 to Quantity, and Column4 to Sales Amount. If Column5 is unnecessary for reporting, it can be removed. The columns can then be arranged in a more logical order.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand column management in Power BI.
• Open Power Query Editor and select the required query.
• Rename columns using different methods.
• Apply consistent naming conventions to column headers.
• Identify and remove unnecessary columns.
• Use Choose Columns and Remove Other Columns appropriately.
• Reorder columns using drag-and-drop and ribbon options.
• Understand the difference between column order and row sorting.
• Use Power Query M functions to rename, remove, and reorder columns.
• Understand Applied Steps and transformation dependencies.
• Validate transformations before loading data into Power BI.

3. Understanding Columns in Power BI

3.1 What Is a Column?

A column is a vertical field in a table that stores one type of information for each record. A sales table may contain columns such as Order ID, Product Name, Quantity, Unit Price, Sales Amount, and Order Date.

Each column has a name and usually a data type, such as text, whole number, decimal number, date, or date/time.

3.2 Why Column Management Is Important

• Improves the readability of datasets.
• Removes irrelevant information from the reporting model.
• Creates a consistent naming structure.
• Makes tables easier to navigate in Power Query Editor.
• Helps analysts understand the meaning of fields.
• Can reduce model size when unnecessary columns are removed.
• Supports a cleaner and more maintainable data preparation process.

3.3 Common Column Management Tasks

4. Introduction to Power Query Editor

Power Query Editor is the data transformation environment in Power BI. It allows users to connect to data sources and prepare information before loading it into the semantic model.

4.1 Steps to Open Power Query Editor

1. Open Power BI Desktop.
2. Select Home from the ribbon.
3. Choose Get Data to import a dataset, or use an existing loaded table.
4. Choose the required source, such as Excel, CSV, or SQL Server.
5. Select Transform Data to open Power Query Editor.
6. Choose the query that contains the columns you want to manage.

4.2 Important Areas of Power Query Editor

5. Renaming Columns in Power BI

5.1 What Does Renaming a Column Mean?

Renaming a column means changing its header to a name that better describes the information stored in that field.

For example, a source file may use abbreviated column names such as CustID, ProdNm, Qty, and Amt. These names can be changed to Customer ID, Product Name, Quantity, and Sales Amount.

5.2 Benefits of Renaming Columns

• Makes the dataset easier to understand.
• Reduces confusion caused by abbreviations.
• Creates consistent naming across reports and datasets.
• Helps other analysts understand the purpose of each field.
• Improves the usability of fields in the Power BI model.
• Makes Power Query steps easier to read and maintain.

5.3 Method 1: Rename by Double-Clicking the Column Header

1. Open Power Query Editor.
2. Select the required query.
3. Locate the column header that needs to be renamed.
4. Double-click the column header.
5. Enter the new column name.
6. Press Enter to apply the change.
7. Verify the new name in the data preview.

Example: Change ProdNm to Product Name.

5.4 Method 2: Rename Using the Right-Click Menu

1. Locate the required column.
2. Right-click its column header.
3. Select Rename from the context menu.
4. Enter the new name.
5. Press Enter.

This method is convenient when working with individual columns.

5.5 Method 3: Rename From the Transform Tab

1. Select the column you want to rename.
2. Open the Transform tab.
3. Locate the Rename command in the ribbon.
4. Select Rename.
5. Enter the new name and confirm the change.

The exact ribbon grouping may vary slightly depending on the Power BI interface version.

5.6 Example of Renaming Columns

Before renaming:

After renaming:

The data values remain unchanged. Only the column headers have been updated.

6. Best Practices for Naming Columns

6.1 Use Meaningful Names

Choose names that clearly describe the data. For example, use Order Date instead of Dt and Customer Name instead of CustNm.

6.2 Maintain Consistent Naming Conventions

Use a consistent naming style throughout the dataset. If the project uses spaces in column names, follow the same style for new or renamed columns.

6.3 Avoid Ambiguous Names

Names such as Value, Amount, or Type may be unclear without context. Prefer Sales Amount, Tax Amount, or Customer Type when appropriate.

6.4 Avoid Duplicate Column Names

Power Query requires column names to be unique within a table. Renaming a column to a name already used by another column can produce a column-name conflict.

For example, if a table already contains Sales and Profit, renaming another column to Sales will cause a conflict. Use a more specific name, such as Total Sales, if it correctly describes the field.

6.5 Avoid Unnecessary Special Characters

Use clear names that work well in formulas and reporting. Avoid unnecessary punctuation and confusing abbreviations. Spaces in column names are supported, but M expressions that reference names containing spaces or special characters may require quoted identifier syntax.

7. Removing Columns in Power BI

7.1 What Does Removing a Column Mean?

Removing a column means excluding a field from the output of a query. This is useful when the source contains information that is not needed for reporting, analysis, or the data model.

For example, a sales file may contain Order ID, Product, Quantity, Sales Amount, Import File Name, Internal Comment, and Temporary Code. If the last three fields are not required downstream, they may be removed after confirming that no later transformation depends on them.

7.2 Benefits of Removing Unnecessary Columns

• Creates a more focused dataset.
• Reduces clutter in Power Query Editor.
• Can reduce the amount of data loaded into the model.
• Makes field selection easier when creating report visuals.
• Helps maintain a clear data structure.
• May improve model memory usage when large unnecessary fields are excluded.

7.3 Method 1: Remove a Single Column

1. Open Power Query Editor.
2. Select the required query.
3. Click the header of the column you want to remove.
4. Open the Home tab.
5. Choose Remove Columns.
6. Verify that the selected column is no longer present.

You can also right-click the column header and select Remove.

7.4 Method 2: Remove Multiple Columns

1. Select the first column to remove.
2. Hold Ctrl and select additional non-adjacent columns, or use Shift to select a contiguous range.
3. Open the Home tab.
4. Choose Remove Columns.
5. Review the output to ensure that only the intended columns were removed.

7.5 Method 3: Remove Other Columns

Remove Other Columns retains the selected columns and removes all the remaining columns from the query.

1. Select the columns that must remain in the dataset.
2. Open the Home tab.
3. Open the Remove Columns menu.
4. Choose Remove Other Columns.
5. Verify that the selected fields remain and the other fields are removed.

Example: A dataset contains Order ID, Product, Quantity, Sales Amount, Internal Note, Import User, and Temporary Code. Select Order ID, Product, Quantity, and Sales Amount, then choose Remove Other Columns to retain only the required fields.

7.6 Method 4: Choose Columns

The Choose Columns command lets you select the fields that should remain by using a list of available column names.

1. Open the Home tab.
2. Select Choose Columns.
3. Review the available column names in the dialog box.
4. Select the checkboxes for the required fields.
5. Clear the checkboxes for unnecessary fields.
6. Confirm the selection.

This method is especially useful when a table contains many columns or when selecting fields by name is easier than locating them in the preview.

8. Understanding Remove Columns Versus Remove Other Columns

Choose the method according to the expected structure of the source data. If the source frequently gains new columns and those columns should be retained for future use, a Remove Other Columns step may exclude them unless the selection is updated.

9. Important Considerations Before Removing Columns

9.1 Check Whether the Column Is Used Later

A column may appear unnecessary in the final report but may be required by a later Power Query step, merge operation, calculated column, or data relationship. Review downstream dependencies before removing it.

9.2 Preserve Important Identifiers

Columns such as Customer ID, Product ID, and Order ID may be essential for joining tables or tracking records. Do not remove them simply because they are not displayed in report visuals.

9.3 Avoid Removing Columns Needed for Validation

Temporary audit fields, source file names, or import timestamps may be useful for troubleshooting. Decide whether they should be retained in the final model or preserved in a separate staging query.

9.4 Consider Future Source Changes

If the source data changes over time, review the removal strategy. Explicitly removing known unnecessary columns and keeping a defined set of required columns have different effects when new fields appear.

10. Reordering Columns in Power BI

10.1 What Is Column Reordering?

Column reordering means changing the sequence in which columns appear in a table. It does not change the values stored in the columns or sort the table's records.

For example, a sales table may originally display Product, Sales Amount, Order Date, Order ID, and Quantity. You can reorder it to display Order ID, Order Date, Product, Quantity, and Sales Amount.

10.2 Why Reorder Columns?

• Places important identifiers near the beginning of a table.
• Groups related fields together.
• Makes data preview easier to read.
• Creates a logical presentation for users inspecting the query.
• Helps organize wide tables with many fields.

10.3 Method 1: Drag and Drop

1. Open Power Query Editor.
2. Locate the column to move.
3. Select and hold the column header.
4. Drag the column to the desired position.
5. Release the mouse button.
6. Verify the new column order.

10.4 Method 2: Using the Move Commands

Power Query versions may provide column-movement options through the column context menu or ribbon commands.

1. Select the column you want to move.
2. Right-click the column header or locate the available Move commands.
3. Choose an appropriate option, such as moving the column left, right, to the beginning, or to the end, depending on the interface.
4. Inspect the new arrangement.

10.5 Method 3: Reordering Multiple Columns

When a dataset contains many columns, organize the table by grouping related fields into a logical sequence. For example, put transaction identifiers first, transaction details next, and financial measures last.

Depending on the interface and available commands, moving columns individually or using an explicit M transformation may be the most reliable approach.

11. Example of Column Reordering

Consider the following sales dataset.

Before reordering:

After reordering:

The order of the columns has changed, but the underlying records and values remain the same.

12. Column Reordering Versus Row Sorting

Column reordering and row sorting are different operations.

Use column movement commands when changing field order. Use sorting commands when arranging records according to dates, names, amounts, or other values.

13. Understanding Applied Steps

Every transformation performed in Power Query is recorded in the Applied Steps section of the Query Settings pane. Common steps include Renamed Columns, Removed Columns, Removed Other Columns, and Reordered Columns.

13.1 Why Applied Steps Matter

• Shows the sequence of data transformations.
• Allows users to review previous operations.
• Makes the cleaning process easier to understand.
• Helps troubleshoot errors caused by earlier steps.
• Enables Power Query to repeat transformations during refresh.

13.2 Editing or Removing an Applied Step

1. Locate the Applied Steps pane.
2. Select the step you want to inspect.
3. Review the resulting data preview.
4. Use the available settings or formula bar to modify the transformation if supported.
5. To remove a step, use its delete control or context menu.
6. Review all subsequent steps for errors or unexpected results.

Important: Removing or modifying an earlier step can affect later steps. For example, if a column is renamed and a later step references the old name, the later step may need to be updated.

14. Using Power Query M Code

Power Query M is the language used to describe data transformations. The graphical interface creates M expressions automatically, but understanding the language helps users build repeatable data preparation workflows.

14.1 Renaming a Column With Table.RenameColumns

The Table.RenameColumns function changes the names of specified columns.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RenamedColumns = Table.RenameColumns(
        Source,
        {
            {"CustID", "Customer ID"},
            {"ProdNm", "Product Name"},
            {"Amt", "Sales Amount"}
        }
    )
in
    RenamedColumns

This example renames three columns in an Excel table named SalesData. The source table must contain the original column names shown in the expression.

14.2 Renaming a Column With Missing-Column Protection

When working with sources whose schemas may change, the optional MissingField.Ignore setting can prevent an error if a column to rename does not exist.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RenamedColumns = Table.RenameColumns(
        Source,
        {{"ProdNm", "Product Name"}},
        MissingField.Ignore
    )
in
    RenamedColumns

Use this option carefully. It avoids an error for a missing source column, but it does not guarantee that the expected data is present. Schema validation may still be necessary.

14.3 Removing a Single Column With Table.RemoveColumns

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RemovedColumns = Table.RemoveColumns(
        Source,
        {"InternalNote"}
    )
in
    RemovedColumns

This example removes InternalNote from the output table.

14.4 Removing Multiple Columns

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RemovedColumns = Table.RemoveColumns(
        Source,
        {"InternalNote", "TemporaryCode", "ImportUser"}
    )
in
    RemovedColumns

Multiple column names can be supplied in a list. Ensure that the selected fields are not required by subsequent transformations.

14.5 Removing Columns With Missing-Column Protection

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RemovedColumns = Table.RemoveColumns(
        Source,
        {"InternalNote", "TemporaryCode"},
        MissingField.Ignore
    )
in
    RemovedColumns

MissingField.Ignore allows the query to continue if one of the listed columns does not exist. This can be useful when source files vary, but the output schema should still be checked to ensure it meets reporting requirements.

14.6 Keeping Only Selected Columns With Table.SelectColumns

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    SelectedColumns = Table.SelectColumns(
        Source,
        {"OrderID", "OrderDate", "Product", "Quantity", "SalesAmount"}
    )
in
    SelectedColumns

This function keeps only the listed columns. It is the M equivalent of selecting the required fields using Choose Columns or Remove Other Columns.

14.7 Reordering Columns With Table.ReorderColumns

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ReorderedColumns = Table.ReorderColumns(
        Source,
        {"OrderID", "OrderDate", "Product", "Quantity", "SalesAmount"}
    )
in
    ReorderedColumns

The specified columns appear in the given order. If the source contains additional columns not listed, the function's default behavior may produce an error. To place the listed columns first while retaining other columns, use MissingField.Ignore as the optional third argument.

14.8 Reordering Columns While Retaining Other Columns

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ReorderedColumns = Table.ReorderColumns(
        Source,
        {"OrderID", "OrderDate", "Product", "Quantity", "SalesAmount"},
        MissingField.Ignore
    )
in
    ReorderedColumns

With MissingField.Ignore, columns missing from the requested ordering list are ignored, while other existing columns can remain in the table. This is useful when the source schema varies, although you should still validate the resulting column order.

14.9 Combining Renaming, Removing, and Reordering

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RenamedColumns = Table.RenameColumns(
        Source,
        {
            {"OrderID", "Order ID"},
            {"OrderDate", "Order Date"},
            {"SalesAmount", "Sales Amount"}
        }
    ),
    RemovedColumns = Table.RemoveColumns(
        RenamedColumns,
        {"InternalNote"},
        MissingField.Ignore
    ),
    ReorderedColumns = Table.ReorderColumns(
        RemovedColumns,
        {"Order ID", "Order Date", "Product", "Quantity", "Sales Amount"},
        MissingField.Ignore
    )
in
    ReorderedColumns

This example renames selected columns, removes an unnecessary field if it exists, and rearranges the remaining columns into a logical order. It assumes the source contains the original columns needed by the renaming step.

15. Complete Practical Example: Cleaning a Sales Table

Imagine that a retail company receives a sales file from several branches. The imported table includes unclear column names, internal fields, and a column order that makes the dataset difficult to read.

15.1 Original Dataset

15.2 Step 1: Rename the Columns

Rename the columns as follows:

15.3 Step 2: Remove Unnecessary Columns

Review TempCode and ImportUser. If these fields are not required for the final report, validation, relationships, or future transformations, remove them.

15.4 Step 3: Reorder the Columns

Arrange the remaining fields as follows:

1. Order ID
2. Product Name
3. Branch
4. Quantity
5. Sales Amount

15.5 Step 4: Validate the Output

Cleaned dataset:

The resulting table has meaningful column names, excludes unnecessary fields, and presents the remaining fields in a logical order. The records and values have not been changed by these column-management operations.

16. Troubleshooting Common Problems

17. Best Practices for Professional Power BI Projects

• Rename unclear column headers as early as practical in the transformation process.
• Use consistent naming conventions across related tables.
• Avoid duplicate column names and unnecessary abbreviations.
• Remove fields only after checking their business and technical purpose.
• Keep important identifiers needed for relationships and joins.
• Use Choose Columns when selecting a known set of required fields.
• Understand how Remove Other Columns affects newly added source columns.
• Reorder columns to improve readability without confusing the operation with sorting rows.
• Review Applied Steps whenever an earlier transformation changes.
• Use MissingField.Ignore only when missing source columns are acceptable.
• Validate the final schema after refreshing the query.
• Maintain a staging query when the original source structure must remain available for troubleshooting.

18. Practical Exercises

Exercise 1: Rename Columns

Create a dataset containing CustID, CustName, ProdNm, Qty, and Amt.

• Import the dataset into Power BI.
• Rename the fields to Customer ID, Customer Name, Product Name, Quantity, and Sales Amount.
• Check that each new name is unique and meaningful.
• Inspect the Renamed Columns step.

Exercise 2: Remove Unnecessary Columns

Create an employee dataset containing Employee ID, Employee Name, Department, Salary, Temporary Code, and Import User.

• Identify the columns required for your analysis.
• Remove Temporary Code if it is not required.
• Use Choose Columns to retain the fields needed for the report.
• Compare the original and cleaned table structures.

Exercise 3: Reorder Sales Columns

Create a sales table with Product, Sales Amount, Quantity, Order Date, and Order ID.

• Move Order ID to the first position.
• Place Order Date after Order ID.
• Group product information before financial measures.
• Confirm that no row values have changed.

Exercise 4: Use Power Query M

Use the Advanced Editor to rename at least three columns, remove one unnecessary column, and reorder the remaining fields.

• Use Table.RenameColumns.
• Use Table.RemoveColumns or Table.SelectColumns.
• Use Table.ReorderColumns.
• Refresh the preview and validate the final structure.

19. Interview Questions and Answers

Q1. What is column management in Power BI?

Answer: Column management involves renaming, removing, selecting, and reordering columns to prepare a dataset for analysis and reporting.

Q2. What is Power Query Editor?

Answer: Power Query Editor is the data preparation environment in Power BI used to connect to sources and transform data before loading it into the model.

Q3. How can you rename a column in Power Query?

Answer: Double-click the column header, right-click and choose Rename, or select the Rename command from the Transform tab.

Q4. What is the purpose of renaming columns?

Answer: Renaming improves readability, removes confusing abbreviations, and creates consistent field names.

Q5. How do you remove multiple columns?

Answer: Select the required columns using Ctrl-click or Shift-click and choose Remove Columns from the Home tab.

Q6. What is the difference between Remove Columns and Remove Other Columns?

Answer: Remove Columns deletes selected fields, while Remove Other Columns keeps selected fields and removes all the remaining columns.

Q7. What is the Choose Columns command?

Answer: Choose Columns opens a selection dialog that lets users specify which fields should remain in the query.

Q8. Does removing a column delete rows?

Answer: No. Removing a column removes a field from the table structure. It does not normally remove the records or change their values.

Q9. What is column reordering?

Answer: Column reordering changes the left-to-right position of fields in a table without changing their data values.

Q10. What is the difference between reordering columns and sorting rows?

Answer: Reordering changes the position of columns, while sorting changes the sequence of records according to selected field values.

Q11. What is the purpose of Table.RenameColumns?

Answer: Table.RenameColumns is an M function that changes one or more column names in a table.

Q12. What is the purpose of Table.RemoveColumns?

Answer: Table.RemoveColumns removes specified fields from a table.

Q13. What is the purpose of Table.SelectColumns?

Answer: Table.SelectColumns keeps only the columns specified in the function's column-name list.

Q14. What is the purpose of Table.ReorderColumns?

Answer: Table.ReorderColumns arranges the table's fields in a specified sequence.

Q15. What are Applied Steps?

Answer: Applied Steps is the list of transformations performed on a query. Each step records an operation and contributes to the final result.

Q16. What happens if you rename a column that is used by a later step?

Answer: A later step may fail if it references the old column name. The affected step must be reviewed and updated.

Q17. Why might new columns disappear after a refresh?

Answer: A step such as Remove Other Columns or Table.SelectColumns may retain only an explicitly selected set of columns, excluding newly added fields.

Q18. Why should important ID columns be retained?

Answer: Identifier columns are often required for joins, relationships, record tracking, and data validation.

20. Quick Revision Checklist

• Understand the purpose of column management.
• Rename unclear columns using meaningful names.
• Maintain unique column names.
• Use Remove Columns to delete selected fields.
• Use Remove Other Columns to retain selected fields.
• Use Choose Columns to select fields through a dialog.
• Reorder columns using drag-and-drop or available movement commands.
• Understand the difference between column reordering and row sorting.
• Review Applied Steps after changing earlier transformations.
• Understand Table.RenameColumns, Table.RemoveColumns, Table.SelectColumns, and Table.ReorderColumns.
• Check the final schema and refresh behavior.

21. Conclusion

Renaming, removing, and reordering columns are fundamental data-preparation tasks in Microsoft Power BI. Renaming improves clarity, removing unnecessary fields creates a more focused dataset, and reordering helps organize the table for easier inspection and maintenance.

Power Query Editor provides simple interface commands for these tasks, while Power Query M functions allow the same operations to be automated and repeated during refreshes.

For reliable results, use meaningful names, preserve fields required by relationships and transformations, understand the effects of column-selection steps, and validate the final output before building reports.

To continue learning, explore the Microsoft Power BI Training course, register for the JustAcademy course demo, or visit the JustAcademy website.

22. Additional Learning Resources

• Microsoft Learn: Rename Columns in Power Query
• Microsoft Learn: Choose or Remove Columns
• Microsoft Learn: Table.RenameColumns Function
• Microsoft Learn: Table.RemoveColumns Function
• Microsoft Learn: Table.SelectColumns Function
• Microsoft Learn: Table.ReorderColumns Function